package cn.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.pojo.Provider;
import cn.util.DBUtil;

public class ProviderMapperImp extends DBUtil implements ProviderMapper {

	// @Test
	// public void T() {
	// System.out.println(getProById("1"));
	// }

	@Override
	public List<Provider> getProviderLike(String proName, Integer rowNum, int pagecount) {
		String sql = " select * from `smbms_provider` where 1=1  ";
		List<Object> list = new ArrayList<Object>();
		if (proName != null && !proName.equals("")) {
			sql += " and proName like ? ";
			list.add("%" + proName + "%");
		}
		sql += "  limit ?,? ;";
		list.add(rowNum);
		list.add(pagecount);
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		System.out.println(sql);
		List<Provider> providerList = new ArrayList<Provider>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Provider pro = new Provider();
				pro.setProCode(st.getString("proCode"));
				pro.setProName(st.getString("proName"));
				pro.setProContact(st.getString("proContact"));
				pro.setProPhone(st.getString("proPhone"));
				pro.setProAddress(st.getString("proAddress"));
				pro.setProFax(st.getString("proFax"));
				pro.setId(st.getLong("id"));
				pro.setProDesc(st.getString("proDesc"));
				providerList.add(pro);
			}
			return providerList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return providerList;
	}

	@Override
	public int totalCount(String proName) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet re = null;
		String sql = "select count(id) from smbms_provider where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (proName != null && !proName.equals("")) {
			sql += "and proName like ?;";
			param.add("%" + proName + "%");
		}
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			re = ps.executeQuery();
			if (re.next()) {
				return re.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, re);
		}
		return 0;
	}

	@Override
	public int proRemove(String id) {
		String sql = "delete from smbms_provider where id = ?";
		DBUtil db = new DBUtil();
		Integer[] i = db.cud(Provider.class, sql, id);
		if (i.length > 0) {
			return i[0];
		}
		return 0;
	}

	@Override
	public Integer[] deleteBatch(String[] id) {
		String sql = "delete from smbms_provider where id in (";
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += ");";
		DBUtil db = new DBUtil();
		Integer[] i = db.cud(Provider.class, sql);
		return i;
	}

	@Override
	public Provider getProByProCode(String proCode) {
		String sql = "select * from smbms_provider where proCode=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, proCode);
			st = ps.executeQuery();
			Provider pro = null;
			while (st.next()) {
				pro = new Provider();
				pro.setProCode(st.getString("proCode"));
				pro.setProName(st.getString("proName"));
				pro.setProContact(st.getString("proContact"));
				pro.setProPhone(st.getString("proPhone"));
				pro.setProAddress(st.getString("proAddress"));
				pro.setProFax(st.getString("proFax"));
				pro.setId(st.getLong("id"));
				pro.setProDesc(st.getString("proDesc"));
			}
			return pro;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public Provider getProByPhone(String proPhone) {
		String sql = "select * from smbms_provider where proPhone=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, proPhone);
			st = ps.executeQuery();
			Provider pro = null;
			while (st.next()) {
				pro = new Provider();
				pro.setProCode(st.getString("proCode"));
				pro.setProName(st.getString("proName"));
				pro.setProContact(st.getString("proContact"));
				pro.setProPhone(st.getString("proPhone"));
				pro.setProAddress(st.getString("proAddress"));
				pro.setProFax(st.getString("proFax"));
				pro.setId(st.getLong("id"));
				pro.setProDesc(st.getString("proDesc"));
			}
			return pro;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public Provider getProByName(String proName) {
		String sql = "select * from smbms_provider where proName=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, proName);
			st = ps.executeQuery();
			Provider pro = null;
			while (st.next()) {
				pro = new Provider();
				pro.setProCode(st.getString("proCode"));
				pro.setProName(st.getString("proName"));
				pro.setProContact(st.getString("proContact"));
				pro.setProPhone(st.getString("proPhone"));
				pro.setProAddress(st.getString("proAddress"));
				pro.setProFax(st.getString("proFax"));
				pro.setId(st.getLong("id"));
				pro.setProDesc(st.getString("proDesc"));
			}
			return pro;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public Provider getProByFax(String proFax) {
		String sql = "select * from smbms_provider where proFax=? ;";
		List<Provider> p = new DBUtil().get(Provider.class, sql, proFax);
		return p.get(0);
	}

	@Override
	public int toAddPro(Provider p) {
		String sql = "INSERT INTO `smbms_provider`(`proCode`, `proName`,"
				+ " `proDesc`, `proContact`, `proPhone`, `proAddress`, `proFax`) VALUES (?,?,?,?,?,?,?);";
		String proCode = p.getProCode();
		String proName = p.getProName();
		String proDesc = p.getProDesc();
		String proContact = p.getProContact();
		String proPhone = p.getProPhone();
		String proAddress = p.getProAddress();
		String proFax = p.getProFax();
		Integer[] i = new DBUtil().cud(Provider.class, sql, proCode, proName, proDesc, proContact, proPhone, proAddress,
				proFax);
		return i[0];
	}

	@Override
	public List<Provider> getProByid(String[] id) {
		String sql = " select * from smbms_provider where id =? ;";
		List<Provider> list = new ArrayList<Provider>();
		if (id == null || id.length == 0) {
			return list;
		}
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < id.length; i++) {
				ps.setObject(1, id[i]);
				st = ps.executeQuery();
				while (st.next()) {
					Provider pro = new Provider();
					pro.setProCode(st.getString("proCode"));
					pro.setProName(st.getString("proName"));
					pro.setProContact(st.getString("proContact"));
					pro.setProPhone(st.getString("proPhone"));
					pro.setProAddress(st.getString("proAddress"));
					pro.setProFax(st.getString("proFax"));
					pro.setId(st.getLong("id"));
					pro.setProDesc(st.getString("proDesc"));
					list.add(pro);
				}
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return list;
	}

	@Override
	public Provider getProById(String id) {
		String sql = "select * from smbms_provider where id=? ;";
		List<Provider> p = new DBUtil().get(Provider.class, sql, id);
		return p.get(0);
	}

	@Override
	public Integer[] updatePro(Provider p) {
		String sql = "update smbms_provider set proDesc=?,proContact=?,proPhone=?,proAddress=?,proFax=? where id=? ";
		String proFax = p.getProFax();
		String proDesc = p.getProDesc();
		String proContact = p.getProContact();
		String proPhone = p.getProPhone();
		String proAddress = p.getProAddress();
		Long id = p.getId();
		Integer[] i = new DBUtil().cud(Provider.class, sql, proDesc, proContact, proPhone, proAddress, proFax, id);
		return i;
	}
}
